package org.dashboard.service.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import netscape.javascript.JSObject;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.dashboard.bean.AdDatasource;
import org.dashboard.bean.AdRole;
import org.dashboard.bean.AdUser;
import org.dashboard.constant.Constant;
import org.dashboard.constant.DbMetaQueryConst;
import org.dashboard.constant.DbSourceTypeEnum;
import org.dashboard.dao.AdDatasourceMapper;
import org.dashboard.service.AdDatasourceService;
import org.dashboard.service.TemplateService;
import org.dashboard.utils.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.freemarker.FreeMarkerTemplateUtils;

import java.io.IOException;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;


@Service(interfaceClass = AdDatasourceService.class)
@CacheConfig(cacheManager = Constant.DEFAULT_CACHE_MANAGER, cacheNames = "source")
public class AdDatasourceServiceImpl implements AdDatasourceService {
    private static final Logger logger = LoggerFactory.getLogger(AdDatasourceServiceImpl.class);

    @Autowired
    private AdDatasourceMapper adDatasourceMapper;

    @Autowired
    TemplateService templateService;

    @Override
    @CacheEvict(key = "'datasourceId:' + #datasourceId.toString()")
    public int deleteByPrimaryKey(Integer datasourceId) {
        return adDatasourceMapper.deleteByPrimaryKey(datasourceId);
    }

    @Override
    @CacheEvict(key = "'datasourceId:' + #record.datasourceId.toString()")
    public int insert(AdDatasource record) {
        adDatasourceMapper.insert(record);
        return record.getDatasourceId();
    }

    @Override
    @CacheEvict(key = "'datasourceId:' + #record.datasourceId.toString()")
    public int insertSelective(AdDatasource record) {
        adDatasourceMapper.insertSelective(record);
        return record.getDatasourceId();
    }

    @Override
    @Cacheable(key = "'datasourceId:' + #datasourceId.toString()")
    public AdDatasource selectByPrimaryKey(Integer datasourceId) {
        return adDatasourceMapper.selectByPrimaryKey(datasourceId);
    }

    @Override
    @CacheEvict(key = "'datasourceId:' + #record.datasourceId.toString()")
    public int updateByPrimaryKeySelective(AdDatasource record) {
        return adDatasourceMapper.updateByPrimaryKeySelective(record);
    }

    @Override
    @CacheEvict(key = "'datasourceId:' + #record.datasourceId.toString()")
    public int updateByPrimaryKey(AdDatasource record) {
        return adDatasourceMapper.updateByPrimaryKey(record);
    }


    @Override
    @Transactional
    @CacheEvict(allEntries = true)
    public boolean deleteByPrimaryKeys(ArrayList<Integer> datasourceIds) {
        for (Integer id : datasourceIds) {
            adDatasourceMapper.deleteByPrimaryKey(id);
        }
        return true;
    }

    @Override
    public List<AdDatasource> selectAdDatasources() {
        return adDatasourceMapper.selectAdDatasources();
    }

    @Override
    public List<Object[]> getMetaTableNames(AdDatasource adDatasource) {

        DbSourceTypeEnum dbSourceTypeEnum = DbTypeTools.getDataSourceEnum(adDatasource.getDatasourceType());
        String url = DbTypeTools.getDataSourceUrl(adDatasource.getDatasourceIp(), adDatasource.getDatasourcePort(), adDatasource.getDatasourceDatabase(), dbSourceTypeEnum);
        Properties env = new Properties();
        env.put("url", url);
        env.put("username", adDatasource.getDatasourceUser());
        env.put("password", adDatasource.getDatasourcePassword());
        env.put("driver", DbTypeTools.getDataSourceDriver(dbSourceTypeEnum));

        DbUtilsHelper dbUtilsHelper = new DbUtilsHelper(env);
        QueryRunner runner = dbUtilsHelper.getRunner();
        List<Object[]> arrayListResult = null;
        try {
            switch (dbSourceTypeEnum) {
                case MySql:
                    arrayListResult = runner.query(DbMetaQueryConst.MYSQL_META_TABLES_QUERY, new ArrayListHandler());
                    break;
                case PostgreSql:
                    arrayListResult = runner.query(DbMetaQueryConst.POSTGRESQL_META_TABLES_QUERY, new ArrayListHandler());
                    break;
            }
        } catch (SQLException e) {
            logger.error("getMetaTableNames:", e);
        }
        return arrayListResult;
    }

    @Override
    public List<Object[]> getMetaColumnNames(AdDatasource adDatasource) {
        DbSourceTypeEnum dbSourceTypeEnum = DbTypeTools.getDataSourceEnum(adDatasource.getDatasourceType());
        String url = DbTypeTools.getDataSourceUrl(adDatasource.getDatasourceIp(), adDatasource.getDatasourcePort(), adDatasource.getDatasourceDatabase(), dbSourceTypeEnum);
        Properties env = new Properties();
        env.put("url", url);
        env.put("username", adDatasource.getDatasourceUser());
        env.put("password", adDatasource.getDatasourcePassword());
        env.put("driver", DbTypeTools.getDataSourceDriver(dbSourceTypeEnum));

        List<Object[]> arrayListResult = null;
        try {
            DbUtilsHelper dbUtilsHelper = new DbUtilsHelper(env);
            QueryRunner runner = dbUtilsHelper.getRunner();
            switch (dbSourceTypeEnum) {
                case MySql:
                    arrayListResult = runner.query(DbMetaQueryConst.MYSQL_META_COLUMNS_QUERY, new ArrayListHandler());
                    break;
                case PostgreSql:
                    arrayListResult = runner.query(DbMetaQueryConst.POSTGRESQL_META_COLUMNS_QUERY, new ArrayListHandler());
                    break;
            }
        } catch (SQLException e) {
            logger.error("getMetaTableNames:", e);
        }
        return arrayListResult;
    }

    @Override
    public Boolean datasourceTest(AdDatasource adDatasource) {
        try {
            DbSourceTypeEnum dbSourceTypeEnum = DbTypeTools.getDataSourceEnum(adDatasource.getDatasourceType());
            String url = DbTypeTools.getDataSourceUrl(adDatasource.getDatasourceIp(), adDatasource.getDatasourcePort(), adDatasource.getDatasourceDatabase(), dbSourceTypeEnum);
            Properties env = new Properties();
            env.put("url", url);
            env.put("username", adDatasource.getDatasourceUser());
            env.put("password", adDatasource.getDatasourcePassword());
            env.put("driver", DbTypeTools.getDataSourceDriver(dbSourceTypeEnum));

            DbUtilsHelper dbUtilsHelper = new DbUtilsHelper(env);
            QueryRunner runner = dbUtilsHelper.getRunner();
            switch (dbSourceTypeEnum) {
                case MySql:
                case PostgreSql:
                    runner.execute("select 1");
                    break;
            }
            return true;
        } catch (SQLException e) {
            logger.error("datasourceTest:", e);
            return false;
        }
    }

    @Override
    public List<Map<String, Object>> getDataMapExecuteSql(String sqlStr, String sqlParams, AdDatasource adDatasource, AdUser user, List<AdRole> roles) {
        List<Map<String, Object>> mapListResult = null;
        try {
            DbSourceTypeEnum dbSourceTypeEnum = DbTypeTools.getDataSourceEnum(adDatasource.getDatasourceType());
            String url = DbTypeTools.getDataSourceUrl(adDatasource.getDatasourceIp(), adDatasource.getDatasourcePort(), adDatasource.getDatasourceDatabase(), dbSourceTypeEnum);
            Properties env = new Properties();
            env.put("url", url);
            env.put("username", adDatasource.getDatasourceUser());
            env.put("password", adDatasource.getDatasourcePassword());
            env.put("driver", DbTypeTools.getDataSourceDriver(dbSourceTypeEnum));

            List<Object> querySqlParams = (JSONArray) JSON.parse(sqlParams);

            Map<String, Object> templateModel = new Hashtable<>();

            for (Object querySqlParam : querySqlParams) {
                JSONObject queryParam = (JSONObject) querySqlParam;
                String paramName = queryParam.getString("ParamName");
                String paramValue = queryParam.getString("DefaultValue");
                templateModel.put(paramName, paramValue);
            }

            if (user != null && sqlStr.contains(Constant.DATA_PERMISSION_USER_IDENTITY)) {
                templateModel.put(Constant.DATA_PERMISSION_USER_IDENTITY, user.getUserId().toString());
            }

            if (roles != null && roles.size() > 0 && sqlStr.contains(Constant.DATA_PERMISSION_ROLE_IDENTITY)) {
                templateModel.put(Constant.DATA_PERMISSION_ROLE_IDENTITY, roles.get(0).getRoleId().toString());
            }

            if (roles != null && roles.size() > 0 && sqlStr.contains(Constant.DATA_PERMISSION_ROLES_IDENTITY)) {
                templateModel.put(Constant.DATA_PERMISSION_ROLES_IDENTITY, String.format("(%s)", roles.stream().map(AdRole::getRoleId).map(Object::toString).collect(Collectors.joining(","))));
            }

            if (!templateModel.isEmpty()) {
                try {
                    Template template = templateService.createTemplate("QuerySqlString_" + EncryptUtil.md5(sqlStr), sqlStr);
                    sqlStr = FreeMarkerTemplateUtils.processTemplateIntoString(template, templateModel);
                } catch (IOException | TemplateException e) {
                    logger.error("FreeMarkerTemplateUtils", e);
                }
            }


            DbUtilsHelper dbUtilsHelper = new DbUtilsHelper(env);
            QueryRunner runner = dbUtilsHelper.getRunner();
            switch (dbSourceTypeEnum) {
                case MySql:
                case PostgreSql:
                    sqlStr = sqlStr.replaceAll("\\r|\\n", " ");
                    mapListResult = runner.query(sqlStr, new MapListHandler());
                    break;
            }
        } catch (SQLException e) {
            logger.error("getColumnsExecuteSql:", e);
        }
        return mapListResult;
    }


    @Override
    public List<Map<String, Object>> getDataMapInvokeService(String urlService, String urlFunction, String urlParams, AdUser user, List<AdRole> roles) {
        List<Map<String, Object>> mapListResult = new ArrayList<>();
        try {
            String queryUrlAddress = urlService;
            List<Object> queryUrlParams = (JSONArray) JSON.parse(urlParams);

            for (Object querySqlParam : queryUrlParams) {
                JSONObject queryParam = (JSONObject) querySqlParam;
                String paramName = queryParam.getString("ParamName");
                String paramValue = queryParam.getString("DefaultValue");

                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", paramName), paramValue);
            }

            if (user != null && queryUrlAddress.contains(String.format("${%s}", Constant.DATA_PERMISSION_USER_IDENTITY))) {
                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", Constant.DATA_PERMISSION_USER_IDENTITY), user.getUserId().toString());
            }

            if (roles != null && roles.size() > 0 && queryUrlAddress.contains(String.format("${%s}", Constant.DATA_PERMISSION_ROLE_IDENTITY))) {
                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", Constant.DATA_PERMISSION_ROLE_IDENTITY), roles.get(0).getRoleId().toString());
            }

            if (roles != null && roles.size() > 0 && queryUrlAddress.contains(String.format("${%s}", Constant.DATA_PERMISSION_ROLES_IDENTITY))) {
                queryUrlAddress = queryUrlAddress.replace(String.format("${%s}", Constant.DATA_PERMISSION_ROLES_IDENTITY), roles.stream().map(AdRole::getRoleId).map(Object::toString).collect(Collectors.joining(",")));
            }

            String responseStr = HttpClientUtil.sendHttpGet(queryUrlAddress);
            logger.debug(responseStr);

            if (!StringUtils.isEmpty(urlFunction)) {
                responseStr = JavaScriptUtil.execute(urlFunction, "parse", responseStr);
                logger.debug(responseStr);
            }
            Object parseObj = JSON.parse(responseStr);

            if (parseObj instanceof JSONObject) {
                mapListResult.add((JSONObject) parseObj);
            } else if (parseObj instanceof JSONArray) {
                List<Object> resultArray = (JSONArray) parseObj;
                for (Object obj : resultArray) {
                    JSONObject jsObj = (JSONObject) obj;
                    mapListResult.add(jsObj);
                }
            }


        } catch (Exception e) {
            logger.error("getDataMapInvokeService:", e);
        }
        return mapListResult;
    }


    @Override
    public PageInfo selectAdDatasourcesPaged(String criteria, Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        List<AdDatasource> adDatasets = adDatasourceMapper.selectAdDatasourcesSearched(criteria);
        return new PageInfo<>(adDatasets);
    }


}
